﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using DataBaseHelper.Properties;

namespace DataBaseHelper
{
    /// <summary>
    /// 定义一组操作SQLServer数据的统一方法
    /// 使用SQLServer的预编译和存储过程
    /// </summary>
    public sealed class SqlDBS : IDBS
    {
        #region private member
        private SqlConnection conn = null;
        private String connstring = Settings.Default.ConnctionString;
        private SqlCommand cmd = null;
        #endregion

        #region stuctor & destructor

        public SqlDBS(string ConnctionString)
        {
            try
            {
                connstring = ConnctionString;
                conn = new SqlConnection(ConnctionString);
            }
            catch (SqlException ex)
            {
                throw new DBMSException(ex);
            }
        }

        public SqlDBS()
        {
            try
            {
                conn = new SqlConnection(connstring);
            }
            catch (SqlException ex)
            {
                throw new DBMSException(ex);
            }
        }
        #endregion


        #region IDBS 成员

        public void Open()
        {
            if (conn.State != ConnectionState.Open)
            {
                try
                {
                    conn.Open();
                }
                catch (SqlException ex)
                {
                    LastError = new DBMSException(ex);
                    throw LastError;
                }
            }
        }

        public void Close()
        {
            try
            {
                cmd.Dispose();
                cmd = null;
                conn.Close();
            }
            catch (SqlException ex)
            {
                LastError = new DBMSException(ex);
                throw LastError;
            }
            catch (Exception ex)
            {
                LastError = new DBMSException(ex);
            }
        }

        public bool Prepare(string sql)
        {
            try
            {
                this.Open();
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                cmd = new SqlCommand(sql, conn);
                cmd.Prepare();
            }
            catch (DBMSException ex)
            {
                LastError = new DBMSException(ex);
                return false;
            }
            catch (Exception ex)
            {
                LastError = new DBMSException(ex);
                return false;
            }
            return true;
        }

        public DbParameter CreateParameter()
        {
            return new SqlParameter();
        }

        public bool Prepare(string sql, DbParameter[] Parameters)
        {
            try
            {
                this.Open();
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                cmd = new SqlCommand(sql, conn);
                foreach (var para in Parameters)
                {
                    cmd.Parameters.Add(para);
                }
                cmd.Prepare();
            }
            catch (DBMSException ex)
            {
                LastError = new DBMSException(ex);
                return false;
            }
            catch (Exception ex)
            {
                LastError = new DBMSException(ex);
                return false;
            }
            return true;
        }

        public bool SetParam(int index, object value)
        {
            try
            {
                if (cmd == null)
                {
                    throw new InvalidOperationException("Please First Prepare a SQL!");
                }
                if (value == null)
                {
                    value = DBNull.Value;
                }
                cmd.Parameters[index].Value = value;
                return true;
            }
            catch (InvalidOperationException ex)
            {
                LastError = new DBMSException(ex);
            }
            catch (Exception ex)
            {
                LastError = new DBMSParamException("" + index, ex);
            }
            return false;
        }

        public bool SetParam(string name, object value)
        {
            try
            {
                if (cmd == null)
                {
                    throw new InvalidOperationException("Please First Prepare a SQL!");
                }
                if (value == null)
                {
                    value = DBNull.Value;
                }
                cmd.Parameters[name].Value = value;
                return true;
            }
            catch (InvalidOperationException ex)
            {
                LastError = new DBMSException(ex);
            }
            catch (Exception ex)
            {
                LastError = new DBMSParamException(name, ex);
            }
            return false;
        }

        public bool BindParams(params object[] paras)
        {
            try
            {
                if (cmd == null)
                {
                    throw new InvalidOperationException("Please First Prepare a SQL!");
                }
                int index = 0;
                foreach (object value in paras)
                {
                    if (value == null)
                    {
                        cmd.Parameters[index].Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters[index].Value = value;
                    }
                }
                return true;
            }
            catch (InvalidOperationException ex)
            {
                LastError = new DBMSException(ex);
            }
            catch (Exception ex)
            {
                LastError = new DBMSParamException("paras", ex);
            }
            return false;
        }

        public bool ExecuteQuery(out DataSet result)
        {
            result = new DataSet();
            try
            {
                using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
                {
                    adp.Fill(result);
                    return true;
                }
            }
            catch (Exception ex)
            {
                LastError = new DBMSException(ex);
            }
            finally
            {
                this.Close();
            }
            return false;
        }

        public int ExecuteNonQuery()
        {
            try
            {
                int r = cmd.ExecuteNonQuery();
                return r;
            }
            catch (Exception ex)
            {
                LastError = new DBMSException(ex);
            }
            finally
            {
                this.Close();
            }
            return -1;
        }

        public Exception LastError { get; private set; }
        #endregion
    }
}
